﻿Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data.OleDb
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.HSSF
Imports NPOI.HSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util

Partial Class WebForm_ManageSalesForecastExport
    Inherits System.Web.UI.Page
    Dim lManager As New LevelManager

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindDDLUploadType()
            Me.BindDDLDivision()
            Me.BindDDLSignature()
            Me.BindCBMMPP()
            Me.BindCBMeterialType()
            Me.BindCBTransferToPLV()
        End If
    End Sub

    Protected Sub ddlDivision_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDivision.SelectedIndexChanged
        Me.BindDDLSignature()
    End Sub

    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
        If Me.CheckSelect() = False Then
            MyObj.ShowMessage(Me.GetLocalResourceObject("CheckSelect_Failure"))
            Return
        End If

        For i As Integer = 0 To cbMeterialType.Items.Count - 1
            If cbMeterialType.Items(i).Selected Then
                Select Case cbMeterialType.Items(i).Value
                    Case "B1", "B2"
                        MyObj.ShowMessage("The quantity of BOM forecast will be calculated by BOM Component, and therefore the BOM option is limited to select when user click the button of Export Forecast by Componet! ")
                        Return
                    Case "AllFG"
                        If Me.HeaderTB = "PLV_" Then
                            MyObj.ShowMessage("PLV Forecast doesn't include the SKUs of All FG(Finish Good & New Launch) and BOM.")
                            Return
                        End If
                    Case "AllPLV", "B2" '－All PLV－,BOM PLV
                        If Me.HeaderTB <> "PLV_" Then
                            MyObj.ShowMessage("SellIn or SellOut Forecast doesn't include the SKUs of 'All PLV' and 'BOM PLV'.")
                            Return
                        End If
                End Select
            End If
        Next
        Dim cmanager As New ChannelManager

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        Dim TableName As String = ""
        '處理Table後面接的年月字串
        If IsNumeric(Me.VersionControl1.Month) And Me.VersionControl1.Month.Length = 1 Then
            TableName = Me.VersionControl1.Year.ToString() & "0" & Me.VersionControl1.Month
        Else
            TableName = Me.VersionControl1.Year.ToString() & Me.VersionControl1.Month
        End If

        Dim MaxDT As Data.DataTable
        Dim dt As Data.DataTable

        '選Trend時,Signature不能選All,Add by angus 20131025
        '準備budget/trend data
        Dim MaxID As String = "" '取得budget/trend data開始計算月份
        Dim YMTableName As String = "" '取得ProductBom年月的字串
        Dim IsMaxID As Boolean = False

        sql.Length = 0
        'sql.AppendFormat("Select MaxID from TCReportRefer where Division='{0}' and TableName='TCReport{1}' ", Me.ddlSignature.SelectedValue, TableName)
        'MaxDT = dat.GetData(sql)

        'If MaxDT.Rows.Count > 0 Then
        '    MaxID = MaxDT.Rows(0)("MaxID").ToString()
        '    YMTableName = MaxID
        '    IsMaxID = True
        '    If Me.VersionControl1.Month = "B" Then
        '        MaxID = 1
        '    Else
        '        If MaxID.Substring(4, 1) = "0" Then
        '            MaxID = MaxID.Substring(5, 1)
        '        Else
        '            MaxID = MaxID.Substring(4, 2)
        '        End If
        '    End If
        'End If

        If Me.VersionControl1.Month = "B" OrElse Me.VersionControl1.Month = "T" Then
            IsMaxID = True

            If Me.VersionControl1.Month = "T" Then
                sql.Length = 0
                sql.AppendFormat("Select MaxID from TCReportRefer where Division='{0}' and TableName='TCReport{1}' ", Me.ddlSignature.SelectedValue, TableName)
                MaxDT = dat.GetData(sql)

                If MaxDT.Rows.Count > 0 Then
                    MaxID = MaxDT.Rows(0)("MaxID").ToString()
                    YMTableName = MaxID
                    If MaxID.Substring(4, 1) = "0" Then
                        MaxID = MaxID.Substring(5, 1)
                    Else
                        MaxID = MaxID.Substring(4, 2)
                    End If
                End If
            Else
                MaxID = 1
                YMTableName = TableName
            End If
        End If

        Dim targetcol, levelName As String
        Dim Level2Row As DataRow = Me.lManager.GetLevelName2(Me.ddlDivision.SelectedValue, Me.ViewPoint)
        levelName = Level2Row("LevelName")
        If Level2Row("Chain") = "Y" Then
            targetcol = String.Format("substring(P.ProdHierarchy,1,{0}*2)", Level2Row("Sequence"))
        Else
            targetcol = String.Format("substring(P.ProdHierarchy,{0}*2-1,2)", Level2Row("Sequence"))
        End If

        Dim IniMonth As Integer = 0
        Dim MonthCount As Integer = 0
        If Me.VersionControl1.Month = "T" OrElse Me.VersionControl1.Month = "B" Then
            If MaxID <> "" Then
                IniMonth = Int32.Parse(MaxID)
            Else
                MyObj.ShowMessage("The Data that you select is not available .")
                Return
            End If
        Else
            IniMonth = Int32.Parse(Me.VersionControl1.Month)
        End If
        MonthCount = IniMonth + 14

        Dim MonthString As String = ""
        Dim MonthStringSF As String = ""
        For i As Integer = IniMonth To MonthCount
            MonthString &= "," & String.Format("sum(isnull(sf.Mon{0},0)*bom.qty) as	Mon{0}", i)
            MonthStringSF &= "," & String.Format("sum(isnull(Mon{0},0)) as Mon{0}", i)
        Next

        Dim BomSkuType As String = "P"
        sql.Length = 0
        '先將資料寫入暫存表,並補齊空值的部分
        sql.AppendFormat("Select Distinct s.SapCode,A.SkuType into #TempA from {0}SFReport{1} s ", Me.HeaderTB, TableName)
        sql.AppendFormat("  inner join ProductData p on s.SapCode=p.SapCode ")
        sql.AppendFormat("Left Join  ")
        Select Case Me.HeaderTB
            Case "SO_"
                sql.AppendFormat("(Select 'Piping' as SkuType union Select 'SellOut') A on 1=1 ")
                BomSkuType = "S"
            Case "PLV_"
                sql.AppendFormat("(Select 'PM' as SkuType union Select 'VIP' union Select 'TRADE' union Select 'P') A on 1=1 ")
                BomSkuType = "P"
            Case Else
                sql.AppendFormat("(Select 'S' as SkuType union Select 'P') A on 1=1 ")
                BomSkuType = "P"
        End Select
        sql.AppendFormat("Where Version1='M' and s.Skutype='S' and ChannelID='' ")
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMMPP.SelectedValue <> "" Then
            For i As Integer = 0 To cbMMPP.Items.Count - 1
                If cbMMPP.Items(i).Selected Then
                    Select Case cbMMPP.Items(i).Value
                        Case "A"
                            sql.AppendFormat("or p.MMPP in ('01','02','03','04','05','06') ")
                        Case "B"
                            sql.AppendFormat("or p.MMPP in ('07','08','09') ")
                        Case Else
                            sql.AppendFormat("or p.MMPP ='{0}' ", cbMMPP.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        If ddlSignature.SelectedValue <> "" Then
            sql.AppendFormat(" and left(p.ProdHierarchy,2)='{0}' ", Left(ddlSignature.SelectedValue, 2))
            If MyObj.IsCatagory(ddlSignature.SelectedItem.Text) Then
                '取下一階Level
                sql.AppendFormat("AND {0} in ", targetcol)
                sql.AppendFormat("({0}) ", MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ddlDivision.SelectedValue, levelName, ddlSignature.SelectedItem.Text)))
            End If
        Else
            Dim stManagerS As New SignatureManager
            sql.AppendFormat(" and left(p.ProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", stManagerS.GetSignatures(Me.ddlDivision.SelectedValue)))
        End If
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMeterialType.SelectedValue <> "" Then
            For i As Integer = 0 To cbMeterialType.Items.Count - 1
                If cbMeterialType.Items(i).Selected Then
                    Select Case cbMeterialType.Items(i).Value
                        Case "AllPLV"
                            sql.AppendFormat("or s.MaterialType not in ('N1','B1','R1','B2') ")
                        Case "AllFG"
                            sql.AppendFormat("or s.MaterialType in ('N1','R1') ")
                        Case "AllBOM"
                            sql.AppendFormat("or s.MaterialType in ('B1','B2') ")
                        Case Else
                            sql.AppendFormat("or s.MaterialType ='{0}' ", cbMeterialType.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")

        sql.AppendFormat("Select ")
        sql.AppendFormat(" Case s.SkuType when 'Piping' then 'P' when 'SellOut' then 'S' ")
        sql.AppendFormat(" when 'PM' then 'S' when 'VIP' then 'S' when 'TRADE' then 'S' ")
        sql.AppendFormat(" else s.SkuType end as SkuType ")
        sql.AppendFormat(",s.SapCode,'' as 'MaterialType','' as 'Brand' ")
        For i As Integer = IniMonth To MonthCount
            sql.AppendFormat(",Sum(isnull(S.Mon{0},0)+isnull(PLV.Mon{0},0)) as Mon{0} ", i)
        Next
        'sql.AppendFormat("into #TempB ")
        sql.AppendFormat("from ")

        '(1)取S、P、PBOM
        '==========================================================================================
        sql.AppendFormat("( ")
        sql.AppendFormat("  Select TA.SapCode,TA.SkuType ")
        For i As Integer = IniMonth To MonthCount
            sql.AppendFormat(",sum(isnull(s.Mon{0},0)+isnull(PBOM.Mon{0},0)) as Mon{0}  ", i)
        Next
        sql.AppendFormat("  from #TempA TA ")

        '取S、P
        '======================================================
        sql.AppendFormat("  left join ")
        sql.AppendFormat("  ( ")
        sql.AppendFormat("      Select s.SapCode,s.SkuType{0} ", MonthStringSF)
        sql.AppendFormat("      from {0}SFReport{1} s ", Me.HeaderTB, TableName)
        sql.AppendFormat("      where s.Version1 ='S' ")
        sql.AppendFormat("      AND s.ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", cmanager.GetChannelIDs(Me.ddlDivision.SelectedValue, Me.ddlSignature.SelectedValue)))
        sql.AppendFormat("      group by s.SapCode,s.SkuType ")
        sql.AppendFormat("  )s on TA.SapCode=s.SapCode and TA.SkuType=s.SkuType ")
        '======================================================

        '取PBOM→P
        '======================================================
        sql.AppendFormat("  left join ")
        sql.AppendFormat("  ( ")
        sql.AppendFormat("	    select bom.BOMSapCode as SapCode,'{0}' as SkuType ", BomSkuType)
        sql.AppendFormat(MonthString)
        sql.AppendFormat("      from ProductBOM{0} bom ", IIf(IsMaxID, YMTableName, TableName))
        'sql.AppendFormat("      join #TempA TA on bom.BomSapCode = TA.SapCode ") 'bom.BomSapCode=小朋友
        sql.AppendFormat("      left join {0}SFReport{1} sf on bom.SapCode = sf.SapCode ", Me.HeaderTB, TableName) 'bom.SapCode=媽媽
        sql.AppendFormat("      AND sf.Version1 ='S'  ")
        sql.AppendFormat("      AND sf.ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", cmanager.GetChannelIDs(Me.ddlDivision.SelectedValue, Me.ddlSignature.SelectedValue)))
        sql.AppendFormat("      group by bom.BOMSapCode ")
        sql.AppendFormat("  ) PBOM on TA.SapCode=PBOM.SapCode and TA.SkuType=PBOM.SkuType ")
        '======================================================
        sql.AppendFormat("  group by TA.SapCode,TA.SkuType ")
        sql.AppendFormat(")s ")
        '==========================================================================================

        '(2)取PLV
        '==========================================================================================
        '取PLV→P
        '======================================================
        sql.AppendFormat("left join ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  Select TA.SapCode,'P' as SkuType{0} ", MonthStringSF)
        sql.AppendFormat("  from (Select distinct SapCode from #TempA) TA ")
        sql.AppendFormat("  Left join {0}SFReport{1} s on TA.SapCode=s.SapCode ", Me.HeaderTB, TableName)
        sql.AppendFormat("  and s.Version1 ='M' and ChannelID='' ")
        Dim Count As Integer = 0
        If Me.cbPLV.SelectedValue <> "" Then
            sql.AppendFormat("  and s.Skutype in ( ")
            For i As Integer = 0 To cbPLV.Items.Count - 1
                If cbPLV.Items(i).Selected Then
                    If Count <> 0 Then sql.AppendFormat(",")
                    sql.AppendFormat("'{0}' ", cbPLV.Items(i).Value)
                    Count += 1
                End If
            Next
            sql.AppendFormat("  ) ")
        Else
            sql.AppendFormat("  and 1=2 ")
        End If
        sql.AppendFormat("  group by TA.SapCode ")
        sql.AppendFormat(")PLV on s.SapCode=PLV.SapCode and s.SkuType=PLV.SkuType ")
        '======================================================
        '==========================================================================================
        sql.AppendFormat("group by ")
        sql.AppendFormat(" Case s.SkuType when 'Piping' then 'P' when 'SellOut' then 'S' ")
        sql.AppendFormat(" when 'PM' then 'S' when 'VIP' then 'S' when 'TRADE' then 'S' ")
        sql.AppendFormat(" else s.SkuType end ")
        sql.AppendFormat(",s.SapCode ")
        sql.AppendFormat("order by s.SapCode ")
        sql.AppendFormat(",Case s.SkuType when 'Piping' then 'P' when 'SellOut' then 'S' ")
        sql.AppendFormat(" when 'PM' then 'S' when 'VIP' then 'S' when 'TRADE' then 'S' ")
        sql.AppendFormat(" else s.SkuType end desc ")
        sql.AppendFormat(";Drop table #TempA ")
        'MyObj.WriteLog(sql.ToString())
        dt = dat.GetData(sql)

        For i As Integer = 0 To dt.Rows.Count - 1
            Select Case dt.Rows(i)("SkuType").ToString()
                Case "Piping"
                    dt.Rows(i)("SkuType") = "P"
                Case "SellOut"
                    dt.Rows(i)("SkuType") = "S"
                Case "PM"
                    dt.Rows(i)("SkuType") = "S"
                Case "VIP"
                    dt.Rows(i)("SkuType") = "S"
                Case "TRADE"
                    dt.Rows(i)("SkuType") = "S"
            End Select
            dt.Rows(i)("SapCode") = MyObj.GetAreaCode() & dt.Rows(i)("SapCode").ToString()
            If dt.Rows(i)("SkuType").ToString() = "S" Then
                dt.Rows(i)("SkuType") = "B"
            End If
            For ii As Integer = 4 To dt.Columns.Count - 1
                If dt.Rows(i)(ii).ToString() = "" Then
                    dt.Rows(i)(ii) = 0
                End If
            Next
        Next

        If dt.Rows.Count > 0 Then
            Me.ExportDTV2(dt, TableName)
        Else
            MyObj.ShowMessage("No data.")
        End If
    End Sub

    Protected Sub btnExportV2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportV2.Click
        If Me.CheckSelect() = False Then
            MyObj.ShowMessage(Me.GetLocalResourceObject("CheckSelect_Failure"))
            Return
        End If

        For i As Integer = 0 To cbMeterialType.Items.Count - 1
            If cbMeterialType.Items(i).Selected Then
                Select Case cbMeterialType.Items(i).Value
                    Case "AllFG", "B1"
                        If Me.HeaderTB = "PLV_" Then
                            MyObj.ShowMessage("PLV Forecast doesn't include the SKUs of All FG(Finish Good & New Launch) and BOM.")
                            Return
                        End If
                    Case "AllPLV", "B2" '－All PLV－,BOM PLV
                        If Me.HeaderTB <> "PLV_" Then
                            MyObj.ShowMessage("SellIn or SellOut Forecast doesn't include the SKUs of 'All PLV' and 'BOM PLV'.")
                            Return
                        End If
                End Select
            End If
        Next

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        Dim TableName As String = ""
        '處理Table後面接的年月字串
        If IsNumeric(Me.VersionControl1.Month) And Me.VersionControl1.Month.Length = 1 Then
            TableName = Me.VersionControl1.Year.ToString() & "0" & Me.VersionControl1.Month
        Else
            TableName = Me.VersionControl1.Year.ToString() & Me.VersionControl1.Month
        End If

        Dim MaxDT As Data.DataTable
        Dim dt As Data.DataTable


        '選Trend時,Signature不能選All,Add by angus 20131025
        '準備budget/trend data
        Dim MaxID As String = "" '取得budget/trend data開始計算月份
        Dim YMTableName As String = "" '取得ProductBom年月的字串
        Dim IsMaxID As Boolean = False

        sql.Length = 0
        'sql.AppendFormat("Select MaxID from TCReportRefer where Division='{0}' and TableName='TCReport{1}' ", Me.ddlSignature.SelectedValue, TableName)
        'MaxDT = dat.GetData(sql)

        'If MaxDT.Rows.Count > 0 Then
        '    MaxID = MaxDT.Rows(0)("MaxID").ToString()
        '    YMTableName = MaxID
        '    IsMaxID = True
        '    If Me.VersionControl1.Month = "B" Then
        '        MaxID = 1
        '    Else
        '        If MaxID.Substring(4, 1) = "0" Then
        '            MaxID = MaxID.Substring(5, 1)
        '        Else
        '            MaxID = MaxID.Substring(4, 2)
        '        End If
        '    End If
        'End If

        If Me.VersionControl1.Month = "B" OrElse Me.VersionControl1.Month = "T" Then
            IsMaxID = True

            If Me.VersionControl1.Month = "T" Then
                sql.Length = 0
                sql.AppendFormat("Select MaxID from TCReportRefer where Division='{0}' and TableName='TCReport{1}' ", Me.ddlSignature.SelectedValue, TableName)
                MaxDT = dat.GetData(sql)

                If MaxDT.Rows.Count > 0 Then
                    MaxID = MaxDT.Rows(0)("MaxID").ToString()
                    YMTableName = MaxID
                    If MaxID.Substring(4, 1) = "0" Then
                        MaxID = MaxID.Substring(5, 1)
                    Else
                        MaxID = MaxID.Substring(4, 2)
                    End If
                End If
            Else
                MaxID = 1
                YMTableName = TableName
            End If
        End If

        Dim targetcol, levelName As String
        Dim Level2Row As DataRow = Me.lManager.GetLevelName2(Me.ddlDivision.SelectedValue, Me.ViewPoint)
        levelName = Level2Row("LevelName")
        If Level2Row("Chain") = "Y" Then
            targetcol = String.Format("substring(P.ProdHierarchy,1,{0}*2)", Level2Row("Sequence"))
        Else
            targetcol = String.Format("substring(P.ProdHierarchy,{0}*2-1,2)", Level2Row("Sequence"))
        End If

        Dim IniMonth As Integer = 0
        Dim MonthCount As Integer = 0
        If Me.VersionControl1.Month = "T" OrElse Me.VersionControl1.Month = "B" Then
            If MaxID <> "" Then
                IniMonth = Int32.Parse(MaxID)
            Else
                MyObj.ShowMessage("The Data that you select is not available .")
                Return
            End If
        Else
            IniMonth = Int32.Parse(Me.VersionControl1.Month)
        End If
        MonthCount = IniMonth + 14

        Dim MonthString As String = ""
        Dim MonthStringSF As String = ""
        For i As Integer = IniMonth To MonthCount
            MonthString &= "," & String.Format("sum(isnull(sf.Mon{0},0)*bom.qty) as	Mon{0}", i)
            MonthStringSF &= "," & String.Format("sum(isnull(Mon{0},0)) as Mon{0}", i)
        Next

        Dim SkuType As String = ""
        Select Case Me.HeaderTB
            Case "SO_"
                SkuType = "'Piping','SellOut'"
            Case "PLV_"
                SkuType = "'PM','VIP','TRADE'"
            Case "WF_"
                SkuType = "'S','N','P'"
            Case ""
                SkuType = "'S','P'"
        End Select

        sql.Length = 0
        '先將資料寫入暫存表,並補齊空值的部分
        sql.AppendFormat("Select Distinct s.SapCode,A.SkuType into #TempA from {0}SFReport{1} s ", Me.HeaderTB, TableName)
        sql.AppendFormat("  inner join ProductData p on s.SapCode=p.SapCode ")
        sql.AppendFormat("Left Join  ")
        sql.AppendFormat("(Select 'S' as SkuType union Select 'P')A on 1=1 ")
        sql.AppendFormat("Where Version1='M' and s.Skutype='S' and ChannelID='' ")
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMMPP.SelectedValue <> "" Then
            For i As Integer = 0 To cbMMPP.Items.Count - 1
                If cbMMPP.Items(i).Selected Then
                    Select Case cbMMPP.Items(i).Value
                        Case "A"
                            sql.AppendFormat("or p.MMPP in ('01','02','03','04','05','06') ")
                        Case "B"
                            sql.AppendFormat("or p.MMPP in ('07','08','09') ")
                        Case Else
                            sql.AppendFormat("or p.MMPP ='{0}' ", cbMMPP.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        If ddlSignature.SelectedValue <> "" Then
            sql.AppendFormat(" and left(p.ProdHierarchy,2)='{0}' ", Left(ddlSignature.SelectedValue, 2))
            If MyObj.IsCatagory(ddlSignature.SelectedItem.Text) Then
                '取下一階Level
                sql.AppendFormat("AND {0} in ", targetcol)
                sql.AppendFormat("({0}) ", MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ddlDivision.SelectedValue, levelName, ddlSignature.SelectedItem.Text)))
            End If
        Else
            Dim stManagerS As New SignatureManager
            sql.AppendFormat(" and left(p.ProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", stManagerS.GetSignatures(Me.ddlDivision.SelectedValue)))
        End If
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMeterialType.SelectedValue <> "" Then
            For i As Integer = 0 To cbMeterialType.Items.Count - 1
                If cbMeterialType.Items(i).Selected Then
                    Select Case cbMeterialType.Items(i).Value
                        Case "AllPLV"
                            sql.AppendFormat("or s.MaterialType not in ('N1','B1','R1','B2') ")
                        Case "AllFG"
                            sql.AppendFormat("or s.MaterialType in ('N1','R1') ")
                        Case "AllBOM"
                            sql.AppendFormat("or s.MaterialType in ('B1','B2') ")
                        Case Else
                            sql.AppendFormat("or s.MaterialType ='{0}' ", cbMeterialType.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")

        'sql.AppendFormat("Select s.SapCode,s.SkuType ")
        sql.AppendFormat("Select s.SkuType,s.SapCode,'' as 'MaterialType','' as 'Brand' ")
        For i As Integer = IniMonth To MonthCount
            sql.AppendFormat(",isnull(S.Mon{0},0) as Mon{0} ", i)
        Next
        sql.AppendFormat("into #TempB ")
        sql.AppendFormat("from ")
        sql.AppendFormat("( ")
        '==================================================================
        '取S+P
        sql.AppendFormat("  Select s.SapCode,'S' as SkuType ")
        For i As Integer = IniMonth To MonthCount
            sql.AppendFormat(",sum(isnull(s.Mon{0},0)) as Mon{0}  ", i)
        Next
        sql.AppendFormat("  from {0}SFReport{1} s ", Me.HeaderTB, TableName)
        sql.AppendFormat("  inner join ProductData p on s.SapCode=p.SapCode ")
        sql.AppendFormat("  where s.SkuType in ({0}) ", SkuType)
        Dim cmanager As New ChannelManager
        sql.AppendFormat("  AND s.ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", cmanager.GetChannelIDs(Me.ddlDivision.SelectedValue, Me.ddlSignature.SelectedValue)))
        sql.AppendFormat("  AND s.Version1 ='S' ")
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMMPP.SelectedValue <> "" Then
            For i As Integer = 0 To cbMMPP.Items.Count - 1
                If cbMMPP.Items(i).Selected Then
                    Select Case cbMMPP.Items(i).Value
                        Case "A"
                            sql.AppendFormat("or p.MMPP in ('01','02','03','04','05','06') ")
                        Case "B"
                            sql.AppendFormat("or p.MMPP in ('07','08','09') ")
                        Case Else
                            sql.AppendFormat("or p.MMPP ='{0}' ", cbMMPP.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        If ddlSignature.SelectedValue <> "" Then
            sql.AppendFormat(" and left(p.ProdHierarchy,2)='{0}' ", Left(ddlSignature.SelectedValue, 2))
            If MyObj.IsCatagory(ddlSignature.SelectedItem.Text) Then
                '取下一階Level
                sql.AppendFormat("AND {0} in ", targetcol)
                sql.AppendFormat("({0}) ", MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ddlDivision.SelectedValue, levelName, ddlSignature.SelectedItem.Text)))
            End If
        Else
            Dim stManagerS As New SignatureManager
            sql.AppendFormat(" and left(p.ProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", stManagerS.GetSignatures(Me.ddlDivision.SelectedValue)))
        End If
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMeterialType.SelectedValue <> "" Then
            For i As Integer = 0 To cbMeterialType.Items.Count - 1
                If cbMeterialType.Items(i).Selected Then
                    Select Case cbMeterialType.Items(i).Value
                        Case "AllPLV"
                            sql.AppendFormat("or s.MaterialType not in ('N1','B1','R1','B2') ")
                        Case "AllFG"
                            sql.AppendFormat("or s.MaterialType in ('N1','R1') ")
                        Case "AllBOM"
                            sql.AppendFormat("or s.MaterialType in ('B1','B2') ")
                        Case Else
                            sql.AppendFormat("or s.MaterialType ='{0}' ", cbMeterialType.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        sql.AppendFormat("  group by s.SapCode ")
        '==================================================================

        sql.AppendFormat("Union ")

        '==================================================================
        '取PLV
        sql.AppendFormat("  Select s.SapCode,'P' as SkuType ")
        '如果沒有選Transfer to,則全帶0
        If Me.cbPLV.SelectedValue = "" Then
            For i As Integer = IniMonth To MonthCount
                sql.AppendFormat("," & String.Format("0 as	Mon{0}", i))
            Next
        Else
            sql.AppendFormat(" {0} ", MonthStringSF)
        End If
        sql.AppendFormat("  from {0}SFReport{1} s ", Me.HeaderTB, TableName)
        sql.AppendFormat("  inner join ProductData p on s.SapCode=p.SapCode ")
        sql.AppendFormat("  where s.Version1 ='M' and ChannelID='' ")
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMMPP.SelectedValue <> "" Then
            For i As Integer = 0 To cbMMPP.Items.Count - 1
                If cbMMPP.Items(i).Selected Then
                    Select Case cbMMPP.Items(i).Value
                        Case "A"
                            sql.AppendFormat("or p.MMPP in ('01','02','03','04','05','06') ")
                        Case "B"
                            sql.AppendFormat("or p.MMPP in ('07','08','09') ")
                        Case Else
                            sql.AppendFormat("or p.MMPP ='{0}' ", cbMMPP.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        If ddlSignature.SelectedValue <> "" Then
            sql.AppendFormat(" and left(p.ProdHierarchy,2)='{0}' ", Left(ddlSignature.SelectedValue, 2))
            If MyObj.IsCatagory(ddlSignature.SelectedItem.Text) Then
                '取下一階Level
                sql.AppendFormat("AND {0} in ", targetcol)
                sql.AppendFormat("({0}) ", MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ddlDivision.SelectedValue, levelName, ddlSignature.SelectedItem.Text)))
            End If
        Else
            Dim stManagerS As New SignatureManager
            sql.AppendFormat(" and left(p.ProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", stManagerS.GetSignatures(Me.ddlDivision.SelectedValue)))
        End If
        sql.AppendFormat("  and (1=2 ")
        If Me.cbMeterialType.SelectedValue <> "" Then
            For i As Integer = 0 To cbMeterialType.Items.Count - 1
                If cbMeterialType.Items(i).Selected Then
                    Select Case cbMeterialType.Items(i).Value
                        Case "AllPLV"
                            sql.AppendFormat("or s.MaterialType not in ('N1','B1','R1','B2') ")
                        Case "AllFG"
                            sql.AppendFormat("or s.MaterialType in ('N1','R1') ")
                        Case "AllBOM"
                            sql.AppendFormat("or s.MaterialType in ('B1','B2') ")
                        Case Else
                            sql.AppendFormat("or s.MaterialType ='{0}' ", cbMeterialType.Items(i).Value)
                    End Select
                End If
            Next
        End If
        sql.AppendFormat("  ) ")
        Dim Count As Integer = 0
        If Me.cbPLV.SelectedValue <> "" Then
            sql.AppendFormat("  and s.Skutype in ( ")
            For i As Integer = 0 To cbPLV.Items.Count - 1
                If cbPLV.Items(i).Selected Then
                    If Count <> 0 Then sql.AppendFormat(",")
                    sql.AppendFormat("'{0}' ", cbPLV.Items(i).Value)
                    Count += 1
                End If
            Next
            sql.AppendFormat("  ) ")
        Else
            sql.AppendFormat("  and 1=2 ")
        End If
        sql.AppendFormat("  group by s.SapCode ")
        '==================================================================

        sql.AppendFormat(")s ")
        sql.AppendFormat("order by s.SapCode,s.SkuType desc ")


        '補回空值資料
        sql.AppendFormat(";Insert into #TempB ")
        'sql.AppendFormat("Select SapCode,SkuType")
        sql.AppendFormat("Select SkuType,SapCode,'',''")
        For i As Integer = IniMonth To MonthCount
            sql.AppendFormat(",0")
        Next
        sql.AppendFormat("from #TempA A ")
        sql.AppendFormat("where not exists ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  Select top 1 * from #TempB B ")
        sql.AppendFormat("  where B.SapCode=A.SapCode  ")
        sql.AppendFormat("  and B.SkuType=A.SkuType  ")
        sql.AppendFormat(") ")
        sql.AppendFormat(";Select * from #TempB order by SapCode,SkuType desc ")
        sql.AppendFormat(";Drop table #TempA ")
        sql.AppendFormat(";Drop table #TempB ")
        dt = dat.GetData(sql)
        'MyObj.WriteLog(sql.ToString())

        For i As Integer = 0 To dt.Rows.Count - 1
            Select Case dt.Rows(i)("SkuType").ToString()
                Case "Piping"
                    dt.Rows(i)("SkuType") = "P"
                Case "SellOut"
                    dt.Rows(i)("SkuType") = "S"
                Case "PM"
                    dt.Rows(i)("SkuType") = "S"
                Case "VIP"
                    dt.Rows(i)("SkuType") = "S"
                Case "TRADE"
                    dt.Rows(i)("SkuType") = "S"
            End Select
            dt.Rows(i)("SapCode") = MyObj.GetAreaCode() & dt.Rows(i)("SapCode").ToString()
            If dt.Rows(i)("SkuType").ToString() = "S" Then
                dt.Rows(i)("SkuType") = "B"
            End If
            For ii As Integer = 4 To dt.Columns.Count - 1
                If dt.Rows(i)(ii).ToString() = "" Then
                    dt.Rows(i)(ii) = 0
                End If
            Next
        Next

        If dt.Rows.Count > 0 Then
            Me.ExportDTV2(dt, TableName)
        Else
            MyObj.ShowMessage("No data.")
        End If
    End Sub

    'Xls
    Public Sub ExportDTV1(ByVal dt As DataTable, ByVal TableName As String)
        Dim ms As MemoryStream
        ms = TryCast(DataTableRenderToExcel.RenderDataTableToExcelNoTitle(dt), MemoryStream)
        ' 設定強制下載標頭。 
        Response.AddHeader("Content-Disposition", String.Format("attachment; filename=SalesForcastReport.xls"))
        ' 輸出檔案。 
        Response.BinaryWrite(ms.ToArray())

        ms.Close()
        ms.Dispose()
    End Sub

    'CSV(逗號分隔)
    Public Sub ExportDTV2(ByVal dt As DataTable, ByVal TableName As String)
        Dim ms As New MemoryStream

        Dim sb As New StringBuilder
        Dim FirstLine As String = "H," & Right(TableName, 2) & Left(TableName, 4) & ",02"

        '加入第一行
        sb.AppendFormat(FirstLine)
        '加入資料
        For i As Integer = 0 To dt.Rows.Count - 1
            sb.AppendFormat(vbCrLf)
            For ii As Integer = 0 To dt.Columns.Count - 1
                If ii <> 0 Then
                    sb.AppendFormat(",")
                End If
                Select Case ii
                    Case 0, 1, 2, 3
                        sb.AppendFormat(dt.Rows(i)(ii).ToString())
                    Case Else
                        Try
                            sb.AppendFormat(Math.Round(Double.Parse(dt.Rows(i)(ii)), 0, MidpointRounding.AwayFromZero))
                        Catch ex As Exception
                            sb.AppendFormat("0")
                        End Try
                End Select
            Next
        Next

        Dim enc As New UTF8Encoding
        Dim arrBytData() As Byte = enc.GetBytes(sb.ToString())
        ms.Write(arrBytData, 0, arrBytData.Length)

        Response.Clear()
        ' 設定強制下載標頭。 
        Response.AddHeader("Content-Disposition", String.Format("attachment; filename=SalesForcastReport({0}).csv", TableName))
        ' 輸出檔案。 
        Response.BinaryWrite(ms.ToArray())

        ms.Close()
        ms.Dispose()
        Response.End()
    End Sub

    Private Function CheckSelect() As Boolean
        If Me.VersionControl1.Month = "T" AndAlso Me.ddlSignature.SelectedValue = "" Then
            Return False
        Else
            Return True
        End If
    End Function

#Region "繫結資料"
    Protected Sub BindDDLUploadType()
        ddlUploadType.DataTextField = "Text"
        ddlUploadType.DataValueField = "Value"
        ddlUploadType.DataSource = MyObj.GetCodeMappingData("ForecastUpload", "UploadType")
        ddlUploadType.DataBind()
    End Sub
    Private Sub BindDDLDivision()
        Dim ddlControl As DropDownList = Me.ddlDivision
        Dim dvManager As New DivisionManager

        ddlControl.DataTextField = "Division"
        ddlControl.DataValueField = "Division"
        ddlControl.DataSource = dvManager.GetAllDivision()
        ddlControl.DataBind()
    End Sub
    Private Sub BindDDLSignature()
        Dim ddlControl As DropDownList = Me.ddlSignature
        Dim stManager As New SignatureManager

        ddlControl.DataTextField = "Description"
        ddlControl.DataValueField = "Signature"
        ddlControl.DataSource = stManager.GetAllSignature(Me.ddlDivision.SelectedValue)
        ddlControl.DataBind()

        ddlControl.Items.Insert(0, MyObj.GetDDLSelectAllItem())
    End Sub
    Private Sub BindCBMMPP()
        Dim cblItem As CheckBoxList = Me.cbMMPP
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.Length = 0
        sql.AppendFormat("select 'Export Continue SKU (MM/PP belong to 01-06)' as Text,'A' as value,1 as Sequence ")
        sql.AppendFormat("union select 'Export Discontinue SKU (MM/PP belong to 07-09)','B',2 ")
        sql.AppendFormat("order by Sequence ")

        cblItem.DataTextField = "Text"
        cblItem.DataValueField = "Value"
        cblItem.DataSource = dat.GetData(sql)

        cblItem.DataBind()
    End Sub
    Private Sub BindCBMeterialType()
        Dim cblItem As CheckBoxList = Me.cbMeterialType
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.Length = 0
        sql.AppendFormat("select Text,Value,1 as sequence from CodeMapping ")
        sql.AppendFormat("where TableName='Product' and ColumnName='MPStatus' ")
        'sql.AppendFormat("and Value in('N1','B1','R1','AllFG') ")
        sql.AppendFormat("and Value in('AllFG') ")
        sql.AppendFormat("union select '－All PLV－','AllPLV',2 ")
        sql.AppendFormat("union select 'BOM','B1',3 ")
        sql.AppendFormat("union select 'BOM PLV','B2',4 ")
        sql.AppendFormat("order by Sequence ")

        cblItem.DataTextField = "Text"
        cblItem.DataValueField = "Value"
        cblItem.DataSource = dat.GetData(sql)

        cblItem.DataBind()
    End Sub
    Private Sub BindCBTransferToPLV()
        Dim cblItem As CheckBoxList = Me.cbPLV
        cblItem.DataTextField = "Text"
        cblItem.DataValueField = "Value"
        cblItem.DataSource = MyObj.GetCodeMappingData("Product", "MPStatus_PLV")
        cblItem.DataBind()
    End Sub

    Public ReadOnly Property ViewPoint() As String
        Get
            Select Case Me.ddlUploadType.SelectedValue
                Case "SellOut"
                    Return "SO_SF"
                Case "PLV"
                    Return "PLV_SF"
                Case "WF"
                    Return "WF_SF"
                Case Else
                    Return "SF"
            End Select
        End Get
    End Property

    Public ReadOnly Property HeaderTB() As String
        Get
            Select Case Me.ddlUploadType.SelectedValue
                Case "SellOut"
                    Return "SO_"
                Case "PLV"
                    Return "PLV_"
                Case "WF"
                    Return "WF_"
                Case Else
                    Return ""
            End Select
        End Get
    End Property
#End Region

End Class
